/*
 =============================================================================
 FILE...............: ClientDAO.java
 COMMENTS...........: Class implemented by using the DAO (Data Access Object)
 					  design pattern for manipulating Client objects while
 					  accessing the database. This class extends the GenericDAO
 					  abstract class.
 =============================================================================
 AUTHOR(S)..........: Everton Ranielly de Sousa Cavalcante
 					  Department of Informatics and Applied Mathematics
 					  Federal University of Rio Grande do Norte
 LAST MODIFICATION..: September 17th, 2012
 ============================================================================= 					  
*/


/* ========================================================================= */
/* PACKAGES			                                                         */
/* ------------------------------------------------------------------------- */
package dao;


/* ========================================================================= */
/* IMPORTS			                                                         */
/* ------------------------------------------------------------------------- */
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Client;


/* ========================================================================= */
/* CLASS ClientDAO                                                           */
/* ------------------------------------------------------------------------- */
/**
 * Class implemented by using the DAO (Data Access Object) design pattern for 
 * manipulating <code>Client</code> objects while accessing the database. 
 * This class extends the <code>GenericDAO</code> abstract class.
 * @author Everton Cavalcante
 */
public class ClientDAO extends GenericDAO<Client, String>
{
	/** Default constructor */
	public ClientDAO()
	{ /* Empty */ }
	
	
	/**
	 * Inserts a client
	 * @param client Client to insert
	 */
	public void insert(Client client)
	{		
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "INSERT INTO clients(title, name, phone, email, creditcardnumber, fidelitynumber) VALUES(?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(query);
			
			// Data to be inserted		
			pstmt.setString(1, client.getTitle());
			pstmt.setString(2, client.getName());
			pstmt.setString(3, client.getPhone());
			pstmt.setString(4, client.getEmail());
			pstmt.setString(5, client.getCreditCardNumber());
			pstmt.setString(6, client.getFidelityNumber());
			
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when inserting client:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Updates a client
	 * @param client Client data to update
	 * @param name Client name
	 */
	public void update(Client client, String name)
	{
		try 
		{
			// Opens a connection 
			openConnection();
				
			// SQL query to be executed
			String query = "UPDATE clients SET title = ?, name = ?, phone = ?, email = ?, creditcardnumber = ?, " +
				"fidelitynumber = ? WHERE name = ?";
			pstmt = conn.prepareStatement(query);
			
			// Data to be updated	
			pstmt.setString(1, client.getTitle());
			pstmt.setString(2, client.getName());
			pstmt.setString(3, client.getPhone());
			pstmt.setString(4, client.getEmail());
			pstmt.setString(5, client.getCreditCardNumber());
			pstmt.setString(6, client.getFidelityNumber());
			pstmt.setString(7, name);
				
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when updating client:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Deletes a client
	 * @param name Client name
	 */
	public void delete(String name)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "DELETE FROM clients WHERE name = ?";
			pstmt = conn.prepareStatement(query);
				
			pstmt.setString(1, name);
			
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when deleting client:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Returns the specified client
	 * @param name Client name
	 * @return Specified flight
	 */
	public Client get(String name)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT title, name, phone, email, creditcardnumber, fidelitynumber FROM clients " +
				"WHERE name = '" + name + "'";
			
			stmt = conn.prepareStatement(query);
				
			// Client to be returned
			Client result = null;
				
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				result = new Client();
				result.setTitle(rs.getString("title"));
				result.setName(rs.getString("name"));
				result.setPhone(rs.getString("phone"));
				result.setEmail(rs.getString("email"));
				result.setCreditCardNumber(rs.getString("creditcardnumber"));
				result.setFidelityNumber(rs.getString("fidelitynumber"));
			}
				
			// Returns the specified client
			return result;
		}
		catch (SQLException e) 
		{
			System.out.println("Error when searching client:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
	
	
	/**
	 * Returns the specified client by its index in the database table
	 * @param index Flight index in the database table
	 * @return Specified client
	 */
	public Client get(int index)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT title, name, phone, email, creditcardnumber, fidelitynumber FROM clients " +
				"WHERE clientid = '" + index + "'";
			
			stmt = conn.prepareStatement(query);
				
			// Client to be returned
			Client result = null;
				
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				result = new Client();
				result.setTitle(rs.getString("title"));
				result.setName(rs.getString("name"));
				result.setPhone(rs.getString("phone"));
				result.setEmail(rs.getString("email"));
				result.setCreditCardNumber(rs.getString("creditcardnumber"));
				result.setFidelityNumber(rs.getString("fidelitynumber"));
			}
				
			// Returns the specified client
			return result;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching client:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
	
	
	/**
	 * Returns the client index in the database table by the client name
	 * @param name Client name
	 * @return Client index in the database table
	 */
	public int getClientIndex(String name) 
	{
		try
		{
			// Opens a connection
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT * FROM clients WHERE name  = '" + name + "'";
			stmt = conn.prepareStatement(query);
					
			// Client index
			int index = 0;
					
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{ index = rs.getInt("clientid"); }
			
			return index;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return 0; 
	}
	
	
	/**
	 * Returns all registered clients
	 * @return Registered clients
	 */
	public List<Client> getAll()
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT title, name, phone, email, creditcardnumber, fidelitynumber FROM clients";
			
			// List of clients
			List<Client> clients = new ArrayList<Client>();
			
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				// Current client
				Client client = new Client();
				
				client.setTitle(rs.getString("title"));
				client.setName(rs.getString("name"));
				client.setPhone(rs.getString("phone"));
				client.setEmail(rs.getString("email"));
				client.setCreditCardNumber(rs.getString("creditcardnumber"));
				client.setFidelityNumber(rs.getString("fidelitynumber"));
				
				// Adds the current clients
				clients.add(client);
			}
			
			// Returns registered clients
			if (clients.isEmpty())
			{
				System.out.println("Error:\nNo clients registered");
				System.exit(1);
			}
			
			return clients;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching clients:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
}
